
/****** Object:  StoredProcedure [dbo].[spGetTongNhapXuatValueByTerm]    Script Date: 10/09/2012 09:26:56 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetTongNhapXuatValueByTerm]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetTongNhapXuatValueByTerm]
GO

/****** Object:  StoredProcedure [dbo].[spGetTongNhapXuatValueByTerm]    Script Date: 10/09/2012 09:26:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spGetTongNhapXuatValueByTerm] 
	@month as int,
	@year as int
WITH ENCRYPTION	
AS
	DECLARE @date_string AS VARCHAR(10)
	DECLARE @date AS DATETIME
	SET @date_string = CAST(@month as varchar(2)) + '/' + '1' + '/' + CAST(@year AS VARCHAR(4))
	SET @date = CAST(@date_string as datetime)
	SET @date = DATEADD(M, 1, @date)
	
	SELECT t1.MaLuuKho, t1.TongNhap, ISNULL(t2.TongXuat, 0) as TongXuat, (t1.TongNhap - ISNULL(t2.TongXuat, 0)) as SLTon
	FROM 
	  ( select sp.TenHoatChat, sp.TenBietDuoc,lk.MaLuuKho, lk.MaThuoc, lk.SoLo, lk.HanSuDung, SUM(ctn.SoLuong) as TongNhap
		from tbl_SanPham sp
		inner join tbl_LuuKho lk on sp.MaThuoc = lk.MaThuoc
		left outer join tbl_ChiTietPhieuNhap ctn on lk.MaLuuKho = ctn.MaLuuKho
		inner join tbl_PhieuNhap pn on ctn.SoPN = pn.SoPN
		--where pn.NgayNhapKho < @date
		where MONTH(pn.NgayNhapKho) = @month AND YEAR(pn.NgayNhapKho) = @year
		group by sp.TenHoatChat, sp.TenBietDuoc, lk.MaLuuKho, lk.MaThuoc, lk.SoLo, lk.HanSuDung 
	  ) as t1
	  LEFT OUTER JOIN
	  (  select sp.TenHoatChat, sp.TenBietDuoc, lk.MaLuuKho, lk.MaThuoc, lk.SoLo, lk.HanSuDung, SUM(ctx.SoLuong) as TongXuat
		from tbl_SanPham sp
		inner join tbl_LuuKho lk on sp.MaThuoc = lk.MaThuoc
		left outer join tbl_ChiTietPhieuXuat ctx on lk.MaLuuKho = ctx.MaLuuKho
		inner join tbl_PhieuXuat px on ctx.SoPX = px.SoPX
		--where px.NgayXuatKho < @date
		where MONTH(px.NgayXuatKho) = @month AND YEAR(px.NgayXuatKho) = @year
		group by sp.TenHoatChat, sp.TenBietDuoc, lk.MaLuuKho, lk.MaThuoc, lk.SoLo, lk.HanSuDung
	  ) as t2	 
	ON t1.MaThuoc = t2.MaThuoc AND
	   t1.MaLuuKho = t2.MaLuuKho AND
	   t1.SoLo = t2.SoLo
	
	 UNION ALL
	 
	 SELECT t2.MaLuuKho, ISNULL(t1.TongNhap, 0) as TongNHap, t2.TongXuat, (t1.TongNhap - ISNULL(t2.TongXuat, 0)) as SLTon
	 FROM 
	  ( select sp.TenHoatChat, sp.TenBietDuoc,lk.MaLuuKho, lk.MaThuoc, lk.SoLo, lk.HanSuDung, SUM(ctn.SoLuong) as TongNhap
		from tbl_SanPham sp
		inner join tbl_LuuKho lk on sp.MaThuoc = lk.MaThuoc
		left outer join tbl_ChiTietPhieuNhap ctn on lk.MaLuuKho = ctn.MaLuuKho
		inner join tbl_PhieuNhap pn on ctn.SoPN = pn.SoPN
		--where pn.NgayNhapKho < @date
		where MONTH(pn.NgayNhapKho) = @month AND YEAR(pn.NgayNhapKho) = @year
		group by sp.TenHoatChat, sp.TenBietDuoc, lk.MaLuuKho, lk.MaThuoc, lk.SoLo, lk.HanSuDung 
	  ) as t1
	  RIGHT OUTER JOIN
	  (  select sp.TenHoatChat, sp.TenBietDuoc, lk.MaLuuKho, lk.MaThuoc, lk.SoLo, lk.HanSuDung, SUM(ctx.SoLuong) as TongXuat
		 from tbl_SanPham sp
		inner join tbl_LuuKho lk on sp.MaThuoc = lk.MaThuoc
		left outer join tbl_ChiTietPhieuXuat ctx on lk.MaLuuKho = ctx.MaLuuKho
		inner join tbl_PhieuXuat px on ctx.SoPX = px.SoPX
		--where px.NgayXuatKho < @date
		where MONTH(px.NgayXuatKho) = @month AND YEAR(px.NgayXuatKho) = @year
		group by sp.TenHoatChat, sp.TenBietDuoc, lk.MaLuuKho, lk.MaThuoc, lk.SoLo, lk.HanSuDung
	  ) as t2	 
	ON t1.MaThuoc = t2.MaThuoc AND
	   t1.MaLuuKho = t2.MaLuuKho AND
	   t1.SoLo = t2.SoLo
GO


